package com.qianfeng.shopping.dao;

import com.qianfeng.shopping.entity.CountTimes;
import com.qianfeng.shopping.util.ConnDb;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class CountTimeDao {
    public List<CountTimes> selectAll(){
        List<CountTimes> list   = new ArrayList<>();
        Connection conn = ConnDb.getConnection();
        PreparedStatement pst = null;
        ResultSet rs = null;
        String sql="select\n" +
                "a.accountrealname,count(l.logloginid) as counttimes\n" +
                "from accounts a\n" +
                "left join\n" +
                "loglogin l\n" +
                "on a.accountid = l.accountid\n" +
                "group by a.accountrealname ";
        try {
            pst = conn.prepareStatement(sql);
            rs = pst.executeQuery();
            while(rs.next()){
                CountTimes countTimes = new CountTimes();
                countTimes.setLogTimes(rs.getInt("counttimes"));
                countTimes.setAccountRealName(rs.getString("accountrealname"));
                list.add(countTimes);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            ConnDb.closeDb(rs,pst,conn);
        }
        return list;
    }

    public static void main(String[] args) {
        CountTimeDao dao = new CountTimeDao();
        System.out.println(dao.selectAll());
    }
}
